package com.ruoyi.web.service;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.IsoFields;
@Service
public class C2IntegrationTimeCostService {
    @Autowired
    private DruidDataSource druidDataSource;

    public Map<String, Map<String, Integer>> getMonthlyReleaseData() {
        // 返回结果：自然月 -> { "releaseCount": 释放版本数, "totalCount": 版本总数, "pubCount": 发布版本总数 }
        Map<String, Map<String, Integer>> monthlyData = new TreeMap<>();

        String sql = "SELECT `buildTime`, `releaseStatus`, `pubStatus` FROM `c2_version_info`;";
        System.out.println("Executing SQL: " + sql);

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                LocalDateTime releaseTime = rs.getObject("buildTime", LocalDateTime.class);
                int releaseStatus = rs.getInt("releaseStatus");
                int pubStatus = rs.getInt("pubStatus");

                // 获取自然月，例如 "2024-01"
                String month = getMonthPeriod(releaseTime);

                // 初始化月份数据
                monthlyData.putIfAbsent(month, new HashMap<>());
                Map<String, Integer> monthData = monthlyData.get(month);

                // 初始化计数项
                monthData.putIfAbsent("releaseCount", 0); // releaseStatus in (1, 2, 7)
                monthData.putIfAbsent("totalCount", 0);   // Total records
                monthData.putIfAbsent("pubCount", 0);    // pubStatus = 0

                // 统计版本总数
                monthData.put("totalCount", monthData.get("totalCount") + 1);

                // 统计 releaseStatus in (1, 2, 7)
                if (releaseStatus == 1) {
                    monthData.put("releaseCount", monthData.get("releaseCount") + 1);
                }

                // 统计 pubStatus = 0
                if (pubStatus == 0) {
                    monthData.put("pubCount", monthData.get("pubCount") + 1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return monthlyData;
    }

    public Map<String, Map<String, Integer>> getWeeklyReleaseData() {
        // 返回结果：自然周 -> { "releaseCount": 释放版本数, "totalCount": 版本总数, "pubCount": 发布版本总数 }
        Map<String, Map<String, Integer>> weeklyData = new TreeMap<>();

        String sql = "SELECT `buildTime`, `releaseStatus`, `pubStatus` FROM `c2_version_info`;";
        System.out.println("Executing SQL: " + sql);

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                LocalDateTime releaseTime = rs.getObject("buildTime", LocalDateTime.class);
                int releaseStatus = rs.getInt("releaseStatus");
                int pubStatus = rs.getInt("pubStatus");

                // 获取自然周，例如 "2024-W01"
                String week = getWeekPeriod(releaseTime);

                // 初始化周数据
                weeklyData.putIfAbsent(week, new HashMap<>());
                Map<String, Integer> weekData = weeklyData.get(week);

                // 初始化计数项
                weekData.putIfAbsent("releaseCount", 0); // releaseStatus in (1, 2, 7)
                weekData.putIfAbsent("totalCount", 0);   // Total records
                weekData.putIfAbsent("pubCount", 0);    // pubStatus = 0

                // 统计版本总数
                weekData.put("totalCount", weekData.get("totalCount") + 1);

                // 统计 releaseStatus in (1, 2, 7)
                if (releaseStatus == 1) {
                    weekData.put("releaseCount", weekData.get("releaseCount") + 1);
                }

                // 统计 pubStatus = 0
                if (pubStatus == 0) {
                    weekData.put("pubCount", weekData.get("pubCount") + 1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return weeklyData;
    }

    public Map<String, Map<String, Integer>> getDataByQuarter() {
        // 返回结果：自然季度 -> { "releaseCount": 释放版本数, "totalCount": 版本总数, "pubCount": 发布版本总数 }
        Map<String, Map<String, Integer>> quarterlyData = new TreeMap<>();

        String sql = "SELECT `buildTime`, `releaseStatus`, `pubStatus` FROM `c2_version_info`;";
        System.out.println("Executing SQL: " + sql);

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                LocalDateTime releaseTime = rs.getObject("buildTime", LocalDateTime.class);
                int releaseStatus = rs.getInt("releaseStatus");
                int pubStatus = rs.getInt("pubStatus");

                // 获取自然季度，例如 "2024-Q1"
                String quarter = getQuarterPeriod(releaseTime);

                // 初始化季度数据
                quarterlyData.putIfAbsent(quarter, new HashMap<>());
                Map<String, Integer> quarterData = quarterlyData.get(quarter);

                // 初始化计数项
                quarterData.putIfAbsent("releaseCount", 0); // releaseStatus in (1, 2, 7)
                quarterData.putIfAbsent("totalCount", 0);   // Total records
                quarterData.putIfAbsent("pubCount", 0);    // pubStatus = 0

                // 统计版本总数
                quarterData.put("totalCount", quarterData.get("totalCount") + 1);

                // 统计 releaseStatus in (1, 2, 7)
                if (releaseStatus == 1) {
                    quarterData.put("releaseCount", quarterData.get("releaseCount") + 1);
                }

                // 统计 pubStatus = 0
                if (pubStatus == 0) {
                    quarterData.put("pubCount", quarterData.get("pubCount") + 1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return quarterlyData;
    }

    public Map<String, Map<String, Integer>> getYearlyReleaseData() {
        // 返回结果：自然年 -> { "releaseCount": 释放版本数, "totalCount": 版本总数, "pubCount": 发布版本总数 }
        Map<String, Map<String, Integer>> yearlyData = new TreeMap<>();

        String sql = "SELECT `buildTime`, `releaseStatus`, `pubStatus` FROM `c2_version_info`;";
        System.out.println("Executing SQL: " + sql);

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                LocalDateTime releaseTime = rs.getObject("buildTime", LocalDateTime.class);
                int releaseStatus = rs.getInt("releaseStatus");
                int pubStatus = rs.getInt("pubStatus");

                // 获取自然年，例如 "2024"
                String year = String.valueOf(releaseTime.getYear());

                // 初始化年度数据
                yearlyData.putIfAbsent(year, new HashMap<>());
                Map<String, Integer> yearData = yearlyData.get(year);

                // 初始化计数项
                yearData.putIfAbsent("releaseCount", 0); // releaseStatus in (1, 2, 7)
                yearData.putIfAbsent("totalCount", 0);   // Total records
                yearData.putIfAbsent("pubCount", 0);    // pubStatus = 0

                // 统计版本总数
                yearData.put("totalCount", yearData.get("totalCount") + 1);

                // 统计 releaseStatus in (1, 2, 7)
                if (releaseStatus == 1) {
                    yearData.put("releaseCount", yearData.get("releaseCount") + 1);
                }

                // 统计 pubStatus = 0
                if (pubStatus == 0) {
                    yearData.put("pubCount", yearData.get("pubCount") + 1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return yearlyData;
    }


    /**
     * 根据日期获取自然月周期，例如 "2024-01"
     */
    private String getMonthPeriod(LocalDateTime releaseTime) {
        return releaseTime.getYear() + "-" + String.format("%02d", releaseTime.getMonthValue());
    }

    /**
     * 根据日期获取自然周周期，例如 "2024-W01"
     */
    private String getWeekPeriod(LocalDateTime releaseTime) {
        // 将 LocalDateTime 转换为 LocalDate，只保留日期部分
        LocalDate date = releaseTime.toLocalDate();

        // 获取年份和该日期的自然周
        int year = date.getYear();
        int week = releaseTime.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR);

        // 获取该周的开始日期（周一）
        LocalDate startOfWeek = date.with(IsoFields.WEEK_OF_WEEK_BASED_YEAR, week)
                .with(IsoFields.WEEK_BASED_YEAR, year)
                .with(java.time.temporal.TemporalAdjusters.previousOrSame(java.time.DayOfWeek.MONDAY));

        // 获取该周的结束日期（周日）
        LocalDate endOfWeek = startOfWeek.plusDays(6);

        // 使用 "MM/dd" 格式化日期
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM/dd");
        return startOfWeek.format(formatter) + "-" + endOfWeek.format(formatter);
    }
    /**
     * 根据日期获取自然季度周期，例如 "2024-Q1"
     */
    private String getQuarterPeriod(LocalDateTime releaseTime) {
        int year = releaseTime.getYear();
        int month = releaseTime.getMonthValue();
        int quarter = (month - 1) / 3 + 1; // 计算季度，1月-3月为Q1，依此类推
        return year + "-第" + quarter + "季度";
    }
}
